package DBHandle;

import java.io.*;
import java.sql.*;

import com.opensymphony.xwork2.Action;
import com.opensymphony.xwork2.ActionContext;

//添加新商品到数据库
public class EditStock
{
	//变量定义
	private InputStream inputStream;
	DBConn.DBConnManager db=new DBConn.DBConnManager();
	DBHandle.General dbHandle=new DBHandle.General();
	Statement stmt=null;
	ResultSet rs=null;
	String sql="";	
	Connection conn=db.getConnection("Mysql");
	
	public InputStream getInputStream() {
		return this.inputStream;
	}
	
	//保存Stock信息
	public String SaveStock() throws Exception
    {
    	String[] item=(String[])ActionContext.getContext().getParameters().get("item");
    	String[] date=(String[])ActionContext.getContext().getParameters().get("date");
    	String[] place=(String[])ActionContext.getContext().getParameters().get("place");
    	String[] number=(String[])ActionContext.getContext().getParameters().get("number");
    	String[] price=(String[])ActionContext.getContext().getParameters().get("price");
    	int itemID=dbHandle.SelectID("qbx_product", "item", item[0]);
    	float avePrice=Float.parseFloat(price[0])/Float.parseFloat(number[0]);
    	sql="insert into qbx_inventory(product_id,purchase_date,place,number,total_price,average_price) values("+
    		itemID+",'"+date[0]+"','"+place[0]+"',"+number[0]+","+price[0]+","+avePrice+")";
    	String sql1="select sold,discarded,remaining,averagePrice from qbx_product where id = "+itemID;
    	String sql2="update qbx_product set remaining=remaining+"+number[0]+",averagePrice=";
    	//请求数据库连接
		if(conn!=null)
		{
			stmt=conn.createStatement();
			int i=stmt.executeUpdate(sql);
			rs=stmt.executeQuery(sql1);
			while(rs.next())
			{
				int sold=rs.getInt("sold");
				int discarded=rs.getInt("discarded");
				int remaining=rs.getInt("remaining");
				float averagePrice=rs.getFloat("averagePrice");
				float newAveragePrice=(sold+discarded+remaining)*averagePrice+Float.parseFloat(price[0]);
				newAveragePrice=newAveragePrice/(sold+discarded+remaining+Float.parseFloat(number[0]));
				sql2+=newAveragePrice+" where id = "+itemID;
			}
			int j=stmt.executeUpdate(sql2);
			if(i+j==2)
				inputStream=new StringBufferInputStream("true");
			else 
				inputStream=new StringBufferInputStream("false");
			stmt.close();
			db.releaseConnection("Mysql", conn);
		}
		else
		{
			inputStream=new StringBufferInputStream("error");
		}
        return Action.SUCCESS;  
    }
	
	//查询Stock信息
	public String SearchStock() throws Exception
	{
		String[] item=(String[])ActionContext.getContext().getParameters().get("item");
    	String[] category=(String[])ActionContext.getContext().getParameters().get("category");
    	String[] place=(String[])ActionContext.getContext().getParameters().get("place");
    	String[] startTime=(String[])ActionContext.getContext().getParameters().get("start_time");
    	String[] finishTime=(String[])ActionContext.getContext().getParameters().get("finish_time");
    	sql="select A.id,A.product_id,A.number,A.place,A.total_price,A.average_price,A.purchase_date from qbx_inventory A,qbx_product B where A.product_id = B.id and ";
    	if(category[0]!=""||item[0]!="")
    	{
    		if(category[0]!="")
    		{
    			int categoryID=dbHandle.SelectID("qbx_product_category", "name", category[0]);
    			sql+="B.category_id = "+categoryID+" and ";
    		}
    		if(item[0]!="")
        		sql+="B.item = '"+item[0]+"' and ";
    	}
    	if(place[0]!="")
    		sql+="A.place = '"+place[0]+"' and ";   	
    	if(startTime[0]==""&&finishTime[0]=="")
			;
		else if(startTime[0]=="")
			sql+="A.purchase_date <= '"+finishTime[0]+"' and ";
		else if(finishTime[0]=="")
			sql+="A.purchase_date >= '"+startTime[0]+"' and ";
		else 
		{
			if(startTime[0].compareTo(finishTime[0])>0)
				sql+="A.purchase_date between '"+finishTime[0]+"' and '"+startTime[0]+"'"+" and ";
			else
				sql+="A.purchase_date between '"+startTime[0]+"' and '"+finishTime[0]+"'"+" and ";
		}
    	sql+="1=1";
    	if(conn!=null)
		{
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
            String aaDataString="{\"aaData\":[";
			while(rs.next())
			{
			String itemName=dbHandle.SelectInfoString("item", "qbx_product", "id",String.valueOf(rs.getInt("product_id")));
			int categoryID=dbHandle.SelectInfoInt("category_id", "qbx_product", "id",String.valueOf(rs.getInt("product_id")));
			String categoryName=dbHandle.SelectInfoString("name", "qbx_product_category", "id", String.valueOf(categoryID));
			float totalAvePrice=SelectAveragePrice(rs.getInt("product_id"));
			aaDataString+="[\"<input type='checkbox' class='checkOne' value='"+rs.getInt("id")+"'></input>\",\""
						+itemName+"\",\""+categoryName+"\",\""+String.valueOf(rs.getDate("purchase_date"))+"\",\""
						+String.valueOf(rs.getString("place"))+"\",\""+String.valueOf(rs.getInt("number"))+"\",\""
						+String.valueOf(rs.getFloat("total_price"))+"\",\""+String.valueOf(rs.getFloat("average_price"))
						+"\",\""+String.valueOf(totalAvePrice)+"\",\""+"<a href='#' id='editOne' value='"
						+rs.getInt("id")+"'>Edit</a>&nbsp&nbsp"+"<a href='#' id='deleteOne' value='"
						+rs.getInt("id")+"'>Delete</a>"
						+"\"],";
			aaDataString+="";
			}
			if(aaDataString.substring(aaDataString.length()-1).equals(","))
				aaDataString=aaDataString.substring(0,aaDataString.length()-1);
			aaDataString+="]}";
			inputStream=new StringBufferInputStream(aaDataString);
			rs.close();
			stmt.close();
			db.releaseConnection("Mysql", conn); 
		}          
    	
		return Action.SUCCESS;
	}
	
	//查询Individual Inventory信息
	public String SearchInventory() throws Exception
	{
		String[] item=(String[])ActionContext.getContext().getParameters().get("item");
    	String[] category=(String[])ActionContext.getContext().getParameters().get("category");
    	String[] place=(String[])ActionContext.getContext().getParameters().get("place");
    	String[] date=(String[])ActionContext.getContext().getParameters().get("date");
    	sql="select A.product_id,sum(A.number) as number,A.place,sum(A.total_price) as totalPrice,max(A.purchase_date) as date from qbx_inventory A,qbx_product B where A.product_id = B.id and ";
    	if(!category[0].equals("All")||!item[0].equals("All"))
    	{
    		if(!category[0].equals("All"))
    		{
    			int categoryID=dbHandle.SelectID("qbx_product_category", "name", category[0]);
    			sql+="B.category_id = "+categoryID+" and ";
    		}
    		if(!item[0].equals("All"))
        		sql+="B.item = '"+item[0]+"' and ";
    	}
    	if(!place[0].equals("All"))
    		sql+="A.place = '"+place[0]+"' and ";   	
    	if(date[0]!="")
			sql+="A.purchase_date = '"+date[0]+"' and ";
    	sql+="1=1 group by A.place,A.product_id";
    	if(conn!=null)
		{
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
            String aaDataString="{\"aaData\":[";
			while(rs.next())
			{	
			float averagePrice=rs.getFloat("totalPrice")/rs.getInt("number");
			String itemName=dbHandle.SelectInfoString("item", "qbx_product", "id",String.valueOf(rs.getInt("product_id")));
			int sold=dbHandle.SelectInfoInt("sold", "qbx_product", "id", String.valueOf(rs.getInt("product_id")));
			int discarded=dbHandle.SelectInfoInt("discarded", "qbx_product", "id", String.valueOf(rs.getInt("product_id")));
			int remaining=dbHandle.SelectInfoInt("remaining", "qbx_product", "id", String.valueOf(rs.getInt("product_id")));
			float totalAvePrice=SelectAveragePrice(rs.getInt("product_id"));
			
			aaDataString+="[\""+itemName+"\",\""+String.valueOf(rs.getDate("date"))+"\",\""+String.valueOf(rs.getString("place"))+"\",\""
						+String.valueOf(rs.getInt("number"))+"\",\""+String.valueOf(rs.getFloat("totalPrice"))+"\",\""+String.valueOf(averagePrice)
						+"\",\""+String.valueOf(totalAvePrice)+"\",\""+String.valueOf(sold)+"\",\""+String.valueOf(discarded)+"\",\""+String.valueOf(remaining)
						+"\"],";
			aaDataString+="";
			}
			if(aaDataString.substring(aaDataString.length()-1).equals(","))
				aaDataString=aaDataString.substring(0,aaDataString.length()-1);
			aaDataString+="]}";
			inputStream=new StringBufferInputStream(aaDataString);
			rs.close();
			stmt.close();
			db.releaseConnection("Mysql", conn); 
		}          
    	
		return Action.SUCCESS;
	}
	
	//更新Stock信息
	public String UpdateStock() throws Exception
	{
		//获取请求参数
		String[] s_id=(String[])ActionContext.getContext().getParameters().get("s_id");
		String[] s_item=(String[])ActionContext.getContext().getParameters().get("s_item");
		String[] s_date=(String[])ActionContext.getContext().getParameters().get("s_date");
		String[] s_place=(String[])ActionContext.getContext().getParameters().get("s_place");
		String[] s_number=(String[])ActionContext.getContext().getParameters().get("s_number");
		String[] s_price=(String[])ActionContext.getContext().getParameters().get("s_price");
		//查询商品类别ID
		int productID=dbHandle.SelectID("qbx_product", "item", s_item[0]);
		
		//请求数据库连接
		if(conn!=null)
		{
			sql="update qbx_inventory set product_id = '"+productID+"',number ="+s_number[0]+",place = '"+s_place[0]
			    +"',total_price = "+Float.parseFloat(s_price[0])+",average_price = "+Float.parseFloat(s_price[0])/Float.parseFloat(s_number[0])
			    +",purchase_date = '"+s_date[0]
			    +"' where id = "+Integer.parseInt(s_id[0]);
			stmt=conn.createStatement();
			int i=stmt.executeUpdate(sql);
			if(i==1)
				inputStream=new StringBufferInputStream("true");
			else 
				inputStream=new StringBufferInputStream("false");
			stmt.close();
			db.releaseConnection("Mysql", conn);
		}
		else
		{
			inputStream=new StringBufferInputStream("error");
		}
		return Action.SUCCESS;
	}
	
	//删除Stock信息 
	public String DeleteOneStock() throws Exception
	{
		//获取请求参数
		String[] id=(String[])ActionContext.getContext().getParameters().get("id");		
		//请求数据库连接
		if(conn!=null)
		{
			stmt=conn.createStatement();			
			sql="delete from qbx_inventory where id = "+id[0];
			int i=stmt.executeUpdate(sql);
			if(i==1)
				inputStream=new StringBufferInputStream("true");
			else 
				inputStream=new StringBufferInputStream("false");
			stmt.close();
			db.releaseConnection("Mysql", conn);
		}
		else
		{
			inputStream=new StringBufferInputStream("error");
		}
		return Action.SUCCESS;
	}
	
	//删除所有选择的Stock记录
	public String DeleteMoreStock() throws Exception
	{
		//获取请求参数
		String[] id=(String[])ActionContext.getContext().getParameters().get("id");		
		//请求数据库连接
		if(conn!=null)
		{
			String[] ID=id[0].split("#");
			for(int m=0;m<ID.length;m++)
			{
			stmt=conn.createStatement();			
			sql="delete from qbx_inventory where id = '"+ID[m]+"'";
			int i=stmt.executeUpdate(sql);
			if(i==1)
				inputStream=new StringBufferInputStream("true");
			else 
				inputStream=new StringBufferInputStream("false");
			stmt.close();
			db.releaseConnection("Mysql", conn);
			}
		}
		else
		{
			inputStream=new StringBufferInputStream("error");
		}
		return Action.SUCCESS;
	}
	
	//获取平均成本
	private float SelectAveragePrice(int productID) throws Exception
	{
		float temp=0;
		if(conn!=null)
		{			
			String sql1="select averagePrice from qbx_product where id = '"+productID+"'";
			Statement stmt1=conn.createStatement();
			ResultSet rs1=stmt1.executeQuery(sql1);
			while(rs1.next())
				temp=rs1.getFloat("averagePrice");
			rs1.close();
			stmt1.close();
		}
		return temp;
	}
}